Global Internet Business Solutions ~ GIBS

Logging SSIS information to the Windows Event Log

Fri, 24 Mar 2017 10:00:00 GMT

First you need to add, using System.Diagnostics. This provides the hook to the classes that will enable you to push your error to the Windows Event Log. Now create the object to interact with it: EventLog evLog = new EventLog("Application", System.Environment.MachineName, "FlexMLS Image Import"); Read more: http://josef-richberg.squarespace.com/journal/2010/1/31/sending-ssis-information-to-the-windows-event-log.html#ixzz4cEmpUz5s

DNN Region Lookup

Tue, 16 Feb 2016 11:20:00 GMT

(SELECT [Value] FROM Lists where EntryID = (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID))) AS [State]

SQL Server Reporting Services Permissions Error

Wed, 24 Dec 2014 11:33:00 GMT

To get this resolved you need to start you Internet Explorer as Administrator -> Run as Administrator then open the url http:///Reports this will take you to the "SQL Server Reporting Services" Home page Click on the "Folder Settings" button. Click on the "New Role Assignment" button. Add the Group or user name: DOMAIN\USER_NAME, and permissions you have logged in as. Click on Ok button. This should resolve the issue.

Pivot Query for Ventrian Property Agent

Mon, 15 Dec 2014 13:22:00 GMT

I had a need for getting a property details for a SSR report and came up with the following store procedure . . . I thought it might be useful to someone. /****** Object: StoredProcedure [dbo].[GIBS_Rentals_Get_PropertyDetails] Script Date: 12/15/2014 06:52:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GIBS_Rentals_Get_PropertyDetails] ( @PropertyID int ) AS BEGIN SET NOCOUNT ON; DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX), @PropertyIDVarchar AS VARCHAR(10) Set @PropertyIDVarchar = Convert(VARCHAR(10),@PropertyID) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) FROM Ventrian_PropertyAgent_CustomField FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') ,1,1,'') --print @cols set @query = 'SELECT PropertyID,' + @cols + ' from ( SELECT PropertyID , cast(Ventrian_PropertyAgent_PropertyValue.CustomValue as Varchar(50)) AS CustomValue, Ventrian_PropertyAgent_CustomField.Name AS [Name] FROM Ventrian_PropertyAgent_PropertyValue INNER JOIN Ventrian_PropertyAgent_CustomField ON Ventrian_PropertyAgent_PropertyValue.CustomFieldID = Ventrian_PropertyAgent_CustomField.CustomFieldID WHERE Ventrian_PropertyAgent_PropertyValue.PropertyID = ' + @PropertyIDVarchar + ' ) x pivot ( min(CustomValue) for Name in (' + @cols + ') ) p ' --print @query execute(@query) END

Get a List of all Databases on a SQL Server Installation

Fri, 24 Oct 2014 09:57:00 GMT

Get a list of the database names and database size in a sql server instance using tsql: EXEC sp_databases

SQL Timeout on Custom DNN module

Wed, 08 Oct 2014 19:33:00 GMT

Try running . . . . exec sp_updatestats;

Replace Null or Empty Value with String

Tue, 16 Sep 2014 10:28:00 GMT

Select ISNULL(NULLIF(GIBS_FBLineItems.ReportType, ''), '--Undefined--') as ReportType from GIBS_FBLineItems

Identify SQL User Roles

Thu, 18 Oct 2012 09:00:00 GMT

select dp2.name as role, dp1.name as owner from sys.database_principals as dp1 inner join sys.database_principals as dp2 on dp1.principal_id = dp2.owning_principal_id where dp1.name = 'myUserName'

How to Shrink Your DotNetNuke Database Log File / Truncate Transaction Log

Sat, 31 Dec 2011 13:05:00 GMT

PROCEDURE: This article tells you how to shrink a DotNetNuke database’s log file via Microsoft’s SQL Server Management Studio. 1) Log into your Management Studio. 2) Right click on the database 3) Select “Properties” 4) Click on “Options” 5) Change the recovery model to “Simple” 6) Click “OK” 7) Right click on the database 8) Select “Tasks” then “Shrink” and finally “Database” 9) Click “OK” 10) Right click on the database 11) Select “Properties” 12) Click on “Options” 13) Change the recovery model to “Full” 14) You can now restart your website.

Finding the Port of MS SQL Named Instance

Thu, 17 Mar 2011 10:12:00 GMT

When running a named instance of MS SQL the instance will run on a port other than the usual port 1433. To determine the port you can use the following query: DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort', @value = @tcp_port OUTPUT select @tcp_port In my case the instance was named "MSSQL".

Create a New Database From an Existing Database Backup (SQL Server Management Studio)

Mon, 20 Dec 2010 12:52:00 GMT

This topic explains how to create a new database from an existing full database backup. Security Note We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database. Important Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio). To create a new database from an existing full backup After you connect to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. Right-click Databases, point to Restore, and then click Database. Clicking Database opens the Restore Database dialog box. On the General page, the name of the source database appears in the To database list by default. To create the new database, enter its name in the list box. In the To a point in time text box, either use the default (Most recent possible) or click the browse button to select a specific date and time. To specify the source and location of the backup sets to restore, click one of the following options: From database Enter a database name in the list box. From device Click the browse button. This opens the Specify Backup dialog box. In the Backup media list, select one of the listed device types. To select one or more devices for the Backup location list, click Add. After you add the device or devices you want to the Backup location list, click OK to return to the General page. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected. For information about the columns in the Select the backup sets to restore grid, see Restore Database (General Page). To view or select the advanced options, click Options in the Select a page pane. In the Restore options panel, optionally select any of the options: Overwrite the existing database Preserve the replication settings Prompt before restoring each backup Restrict access to the restored database For complete descriptions of theses options of the Restore options panel, see Restore Database (Options Page). You can move the database that you are restoring by specifying new restore destinations for the files in the Restore the database files as grid. The grid lists the original full path for each data or log file of the database and the restore destination for each file. The following table describes the columns of the Restore the database files as grid. Header Values Original File Name The full path of a data file or log file of the original database. Restore As The path and file name that will be used as the restore destination for this data file or log file. To move the database that you are restoring to a different location, edit the Restore As field of each file and specify the new destination path or file name, or both. Also, if you want to restore a copy of a database on the server instance while the original database is still attached, you must specify a new database name in the To database field on the General page of the Restore Database dialog box. Note Changing the restore destinations in the Restore As column is equivalent to using the MOVE option in RESTORE statements. The Recovery state panel determines the state of the database after the restore operation. The default behavior is to: Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) Note Choose this option only if you are restoring all the necessary backups now. Alternatively, you can select to either: Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY) For descriptions of the options of the Restore options panel, see Restore Database (Options Page).